package newFolder;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// Fig. 28.31: PersonQueries.java
// PreparedStatements used by the Address Book application

public class PersonQueries
{

    private Connection connection = null; // manages connection
    private PreparedStatement selectAllPeople = null;
    private PreparedStatement selectPeopleByLastName = null;
    private PreparedStatement insertNewPerson = null;

    // constructor
    public PersonQueries()
    {
        String[] types = {"TABLE"};
        try
        {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:Addresses.db");
            //Class.forName("com.mysql.jdbc.Driver").newInstance();
            //connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            //connection = DriverManager.getConnection("jdbc:mysql://localhost/addresses?" + "user=lance&password=lance");

            Statement batchStatement = connection.createStatement();
            batchStatement.addBatch("DROP TABLE IF EXISTS Addresses;");
            batchStatement.addBatch("CREATE TABLE Addresses ("
                    + "AddressID INTEGER PRIMARY KEY, "
                    + "FirstName VARCHAR (15) NOT NULL, "
                    + "LastName VARCHAR (30) NOT NULL, "
                    + "Email VARCHAR (30) NOT NULL, "
                    + "PhoneNumber VARCHAR (15) NOT NULL );");
            batchStatement.addBatch("INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber)"
                    + "VALUES ('Mike','Green','demo1@deitel.com','555-5555');");
            batchStatement.addBatch("INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber)"
                    + "VALUES ('Mary','Brown','demo2@deitel.com','555-1234');");
            batchStatement.executeBatch();
            DatabaseMetaData metaData = connection.getMetaData();
            //ResultSet tableTypes = metaData.getSchemas();
            System.out.println(metaData.getConnection());

            //stat.executeUpdate("drop table if exists people;");
            //stat.executeUpdate("create table people (name, occupation);");

            // create query that selects all entries in the AddressBook
            selectAllPeople = connection.prepareStatement("SELECT * FROM Addresses");

            // create query that selects entries with a specific last name
            selectPeopleByLastName = connection.prepareStatement("SELECT * FROM Addresses WHERE LastName = ?");

            // create insert that adds a new entry into the database
            insertNewPerson = connection.prepareStatement("INSERT INTO Addresses "
                    + "( FirstName, LastName, Email, PhoneNumber ) "
                    + "VALUES ( ?, ?, ?, ? )");
        } // end try
        catch (ClassNotFoundException ex)
        {
            System.out.println(ex.toString());
        }
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
            System.exit(1);
        } // end catch
    } // end PersonQueries constructor

    // select all of the addresses in the database
    public List<Person> getAllPeople()
    {
        List<Person> results = null;
        ResultSet resultSet = null;

        try
        {
            // executeQuery returns ResultSet containing matching entries
            resultSet = selectAllPeople.executeQuery();
            
            results = new ArrayList<Person>();

            while (resultSet.next())
            {
                results.add(new Person(
                        resultSet.getInt("addressID"),
                        resultSet.getString("firstName"),
                        resultSet.getString("lastName"),
                        resultSet.getString("email"),
                        resultSet.getString("phoneNumber")));
            } // end while
        } // end try
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        } // end catch
        finally
        {
            try
            {
                resultSet.close();
            } // end try
            catch (SQLException sqlException)
            {
                System.out.println(sqlException.toString());
                close();
            } // end catch
        } // end finally

        return results;
    } // end method getAllPeople

    // select person by last name   
    public List<Person> getPeopleByLastName(String name)
    {
        List<Person> results = null;
        ResultSet resultSet = null;

        try
        {
            selectPeopleByLastName.setString(1, name); // specify last name

            // executeQuery returns ResultSet containing matching entries
            resultSet = selectPeopleByLastName.executeQuery();

            results = new ArrayList<Person>();

            while (resultSet.next())
            {
                results.add(new Person(
                        resultSet.getInt("AddressID"),
                        resultSet.getString("firstName"),
                        resultSet.getString("lastName"),
                        resultSet.getString("email"),
                        resultSet.getString("phoneNumber")));
            } // end while
        } // end try
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        } // end catch
        finally
        {
            try
            {
                resultSet.close();
            } // end try
            catch (SQLException sqlException)
            {
                System.out.println(sqlException.toString());
                close();
            } // end catch
        } // end finally

        return results;
    } // end method getPeopleByName

    // add an entry
    public int addPerson(String fname, String lname, String email, String num)
    {
        int result = 0;

        // set parameters, then execute insertNewPerson
        try
        {
            insertNewPerson.setString(1, fname);
            insertNewPerson.setString(2, lname);
            insertNewPerson.setString(3, email);
            insertNewPerson.setString(4, num);

            // insert the new entry; returns # of rows updated
            result = insertNewPerson.executeUpdate();
        } // end try
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
            close();
        } // end catch

        return result;
    } // end method addPerson

    // close the database connection
    public void close()
    {
        try
        {
            connection.close();
        } // end try
        catch (SQLException sqlException)
        {
            System.out.println(sqlException.toString());
        } // end catch
    } // end method close
} // end class PersonQueries
/**************************************************************************
 * (C) Copyright 1992-2010 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. All Rights Reserved.                           *
 *                                                                        *
 * DISCLAIMER: The authors and publisher of this book have used their     *
 * best efforts in preparing the book. These efforts include the          *
 * development, research, and testing of the theories and programs        *
 * to determine their effectiveness. The authors and publisher make       *
 * no warranty of any kind, expressed or implied, with regard to these    *
 * programs or to the documentation contained in these books. The authors *
 * and publisher shall not be liable in any event for incidental or       *
 * consequential damages in connection with, or arising out of, the       *
 * furnishing, performance, or use of these programs.                     *
 *************************************************************************/
